﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace KeepAccounts.Common.NPOI
{
   public  class NpoiExcelUtility
    {
        
        private string _xlsPath = string.Empty;
        private XSSFWorkbook _workBook = null;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="xlsPath">xls保存路径</param>
        /// <param name="TempletFileName">xls模板路径</param>
        public NpoiExcelUtility(string xlsPath, string TempletFileName)
        {
            _xlsPath = this.CheckFilePath(xlsPath);

            FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
           // _workBook = new HSSFWorkbook(file);
            _workBook = new XSSFWorkbook(file);
        }

        /// <summary>
        /// 将DataTable保存到sheet里
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheet"></param>
        private void DataTableToExcel(DataTable dt, ISheet sheet)
        {
            ICellStyle style = _workBook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;

            ICellStyle colStyle = _workBook.CreateCellStyle();
            colStyle.Alignment = HorizontalAlignment.Left;
            colStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font = _workBook.CreateFont();
            font.Color = HSSFColor.LightBlue.Index;
            colStyle.SetFont(font);

            //列名
            //IRow row = sheet.CreateRow(0);
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    sheet.SetDefaultColumnStyle(i, style);

            //    ICell cell = row.CreateCell(i);
            //    cell.SetCellValue(dt.Columns[i].ToString());

            //    cell.CellStyle = colStyle;
            //}
            //内容
            var headerRow = (XSSFRow)sheet.GetRow(0);

            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                row.Height = 50 * 20;
                ICell numcell = row.CreateCell(0);
                numcell.SetCellValue(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object obj = dt.Rows[i - 1][j];
                    if (obj != null)
                    {
                        string ColumnName = dt.Columns[j].ToString();
                        var _Column = headerRow.Cells.Find(t => !string.IsNullOrEmpty(t.StringCellValue) && t.ToString().ToLower() == ColumnName.ToLower());
                        //ICell cell = row.CreateCell(j + 1);             
                        if (_Column != null)
                        {
                            ICell cell = row.CreateCell(_Column.ColumnIndex);
                            if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
                            {
                                cell.SetCellValue(Convert.ToDouble(obj));
                            }
                            else if (obj is bool)
                            {
                                cell.SetCellValue((bool)obj);
                            }
                            else
                            {
                                cell.SetCellValue(obj.ToString());
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 保存Excel
        /// </summary>
        public void SaveExcel()
        {
            FileStream file = new FileStream(_xlsPath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }

        /// <summary>
        /// 创建Sheet
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
        public void ExportResume(string sheetName, DataTable tbl)
        {
            string sName = this.CheckSheetName(sheetName);

            int rowMax = 65535;
            int intNum = tbl.Rows.Count / rowMax;
            int remainder = tbl.Rows.Count % rowMax;

            for (int i = 0; i < intNum; i++)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < 65535; j++)
                {
                    int rowIndex = i * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (i + 1);
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
            if (remainder > 0)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < remainder; j++)
                {
                    int rowIndex = intNum * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (intNum + 1);
                if (intNum < 1)
                {
                    subSheet = sName;
                }
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
        }
        /// <summary>
        /// 创建Sheet
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
        public void CreatExcelSheet(string sheetName, DataTable tbl)
        {
            string sName = this.CheckSheetName(sheetName);

            int rowMax = 65535;
            int intNum = tbl.Rows.Count / rowMax;
            int remainder = tbl.Rows.Count % rowMax;

            for (int i = 0; i < intNum; i++)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < 65535; j++)
                {
                    int rowIndex = i * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (i + 1);
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
            if (remainder > 0)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < remainder; j++)
                {
                    int rowIndex = intNum * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (intNum + 1);
                if (intNum < 1)
                {
                    subSheet = sName;
                }
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
        }

        /// <summary>
        /// 检查sheet名称是否合法，并去掉不合法字符
        /// </summary>
        /// <param name="sheetName"></param>
        private string CheckSheetName(string sheetName)
        {
            string rlt = sheetName;
            string[] illegalChars = { "*", "?", "\"", @"\", "/" };
            for (int i = 0; i < illegalChars.Length; i++)
            {
                rlt = rlt.Replace(illegalChars[i], "");
            }
            return rlt;
        }

        /// <summary>
        ///  检查xls路径是否合法，并去掉不合法字符
        /// </summary>
        /// <param name="filePath"></param>
        private string CheckFilePath(string filePath)
        {
            string dir = Path.GetDirectoryName(filePath);
            string fileName = Path.GetFileNameWithoutExtension(filePath);
            string ext = Path.GetExtension(filePath);

            string[] illegalChars = { ":", "*", "?", "\"", "<", ">", "|", @"\", "/" };
            for (int i = 0; i < illegalChars.Length; i++)
            {
                fileName = fileName.Replace(illegalChars[i], "");
            }
            string rlt = Path.Combine(dir, fileName + ext);
            return rlt;
        }
    }
}
